unit InvoiceListForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, DBModule, Vcl.Grids,
  Vcl.DBGrids, frxRich, frxClass, frxDBSet, frxPreview, Vcl.ComCtrls,
  DateUtils, Data.DB, InvoiceForm, CurrToWords, frxExportPDF, UserUtils,
  frxExportRTF, frxExportImage, frxExportHTML, fs_ipascal, System.StrUtils,
  frxExportXLS, frxExportMail, frxDesgn, InvoiceForm2, frxExportODF,
  Vcl.ExtCtrls;

type
  TPrintOperation = (poPrint, poPreview, poToPDF);

  TfmInvoiceList = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    btnClose: TButton;
    btnPreview: TButton;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    btnPrint: TButton;
    frxDBDataset1: TfrxDBDataset;
    frxDBDataset2: TfrxDBDataset;
    frxReport1: TfrxReport;
    frxRichObject1: TfrxRichObject;
    rbInvoice: TRadioButton;
    rbInvoiceList: TRadioButton;
    Label3: TLabel;
    txtYear: TEdit;
    Label4: TLabel;
    txtMonth: TEdit;
    UpDown1: TUpDown;
    UpDown2: TUpDown;
    btnEdit: TButton;
    StatusBar1: TStatusBar;
    chkFilterByYear: TCheckBox;
    rbBatchPrint: TRadioButton;
    frxPDFExport1: TfrxPDFExport;
    btnExportPDF: TButton;
    frxDBConfig: TfrxDBDataset;
    frxMailExport1: TfrxMailExport;
    frxXLSExport1: TfrxXLSExport;
    btnEdit2: TButton;
    frxRTFExport1: TfrxRTFExport;
    frxODTExport1: TfrxODTExport;
    frxODSExport1: TfrxODSExport;
    chkFilterByMonth: TCheckBox;
    rbQuotation: TRadioButton;
    RadioGroup1: TRadioGroup;
    procedure btnCloseClick(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure FormCreate(Sender: TObject);
    procedure btnPreviewClick(Sender: TObject);
    procedure btnPrintClick(Sender: TObject);
    procedure rbInvoiceListClick(Sender: TObject);
    procedure rbInvoiceClick(Sender: TObject);
    procedure btnEditClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure frxReport1GetValue(const VarName: string; var Value: Variant);
    procedure DBGrid2CellClick(Column: TColumn);
    procedure DBGrid1DblClick(Sender: TObject);
    procedure chkFilterByYearClick(Sender: TObject);
    procedure rbBatchPrintClick(Sender: TObject);
    procedure txtYearChange(Sender: TObject);
    procedure txtMonthChange(Sender: TObject);
    procedure btnExportPDFClick(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    function frxReport1UserFunction(const MethodName: string;
      var Params: Variant): Variant;
    procedure DBGrid1KeyPress(Sender: TObject; var Key: Char);
    procedure btnEdit2Click(Sender: TObject);
    procedure chkFilterByMonthClick(Sender: TObject);
    procedure RadioGroup1Click(Sender: TObject);
  private
    procedure PrintReport(PrintOperation:TPrintOperation);
    procedure SetFilter(Enabled: Boolean);
    procedure DoFilter(Val: Boolean);
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fmInvoiceList: TfmInvoiceList;

implementation

uses
  MainForm;

{$R *.dfm}

procedure TfmInvoiceList.btnCloseClick(Sender: TObject);
begin
  DataModule1.fbInvoiceDetails.Filter := '';
  DataModule1.fbInvoiceDetails.Filtered := False;
  Close;
end;

procedure TfmInvoiceList.btnEditClick(Sender: TObject);
var
//  fmInvoiceEditor: TfmInvoice;
  RTFStream: TMemoryStream;
  RTFString: AnsiString;
  i: integer;
begin
  RTFStream := TMemoryStream.Create;
  RTFStream.Clear;
  RTFString := DBGrid1.DataSource.DataSet.FieldByName('FOR_RTF').AsAnsiString;
  RTFStream.Write(RTFString[1], Length(RTFString));
  RTFStream.Position := 0;
  fmInvoice := TfmInvoice.Create(Self);
  with fmInvoice do
  begin
    InvoiceID := DBGrid1.DataSource.DataSet.FieldByName('ID').AsInteger;
    InvoiceOperation := opEdit;
    Caption := 'Invoice Editor';
    lbDate.Caption := DBGrid1.DataSource.DataSet.FieldByName('INVOICE_DATE').AsString;
    dtDateOfShoot.Date := DBGrid1.DataSource.DataSet.FieldByName('DATE_OF_SHOOT').AsDateTime;
    dtDueDate.Date := DBGrid1.DataSource.DataSet.FieldByName('INVOICE_DUE_DATE').AsDateTime;
    lblInvoiceNo.Caption := DBGrid1.DataSource.DataSet.FieldByName('INVOICE_NO').AsString;
    dbcbCompanyName.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('COMPANY_NAME').AsVariant;
    dbcbCompanyName.Enabled := False;
    with dbcbProduct.ListSource.DataSet do
    begin
      if not Active then
        Open;
      Last;
      First;
    end;
    dbcbProduct.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('FOR_PRODUCT').AsVariant;
    txtPublishOn.Text := DBGrid1.DataSource.DataSet.FieldByName('PUBLISH_ON').AsString;
    txtPersonInCharge.Text := DBGrid1.DataSource.DataSet.FieldByName('PERSON_IN_CHARGE').AsString;
    txtJobsheetNo.Text := DBGrid1.DataSource.DataSet.FieldByName('JOBSHEET_NO').AsString;
    dbcbPhotographer1.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('PHOTOGRAPHER1_ID').AsInteger;
    dbcbPhotographer2.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('PHOTOGRAPHER2_ID').AsInteger;
    lbTotalAmount.Caption := DBGrid1.DataSource.DataSet.FieldByName('TOTAL_AMOUNT').AsString;
    StatusBar1.Panels[0].Text := 'Created on: ' + DBGrid1.DataSource.DataSet.FieldByName('CREATE_DATE').AsString;
    StatusBar1.Panels[1].Text := 'Updated on: ' + DBGrid1.DataSource.DataSet.FieldByName('UPDATE_DATE').AsString;
    frmRTFEditor1.Editor.Lines.LoadFromStream(RTFStream);
    SetLength(DetailsID, DBGrid2.DataSource.DataSet.RecordCount);
    sgDetails.RowCount := DBGrid2.DataSource.DataSet.RecordCount + 1;
    DBGrid2.DataSource.DataSet.First;
    if DBGrid2.DataSource.DataSet.RecordCount > 0 then
      for i := 1 to DBGrid2.DataSource.DataSet.RecordCount do begin
        DetailsID[i - 1] := DBGrid2.DataSource.DataSet.FieldByName('ID').AsInteger;
        sgDetails.Cells[0, i] := IntToStr(i);
        RTFString := DBGrid2.DataSource.DataSet.FieldByName('DESCRIPTION_RTF').AsAnsiString;
        sgDetails.Cells[1, i] := DBGrid2.DataSource.DataSet.FieldByName('DESCRIPTION').AsString;
        sgDetails.Objects[1, i] := TMemoryStream.Create;
        TMemoryStream(sgDetails.Objects[1, i]).Write(RTFString[1], Length(RTFString));
        sgDetails.Cells[2, i] := DBGrid2.DataSource.DataSet.FieldByName('AMOUNT').AsString;
        sgDetails.Cells[3, i] := DBGrid2.DataSource.DataSet.FieldByName('FOC_DI').AsString;
        DBGrid2.DataSource.DataSet.Next;
      end
    else begin
      sgDetails.RowCount := 2;
      sgDetails.FixedRows := 1;
    end;
    RTFStream.Free;
    Bookmark := DBGrid1.DataSource.DataSet.GetBookmark;
    Modified := False;
    Show;
  end;
end;

procedure TfmInvoiceList.btnPreviewClick(Sender: TObject);
begin
  PrintReport(poPreview);
end;

procedure TfmInvoiceList.btnPrintClick(Sender: TObject);
begin
  PrintReport(poPrint);
end;

procedure TfmInvoiceList.btnEdit2Click(Sender: TObject);
var
//  fmInvoiceEditor: TfmInvoice;
  i: integer;
begin
  fmInvoice2 := TfmInvoice2.Create(Self);
  with fmInvoice2 do
  begin
    InvoiceID := DBGrid1.DataSource.DataSet.FieldByName('ID').AsInteger;
    InvoiceOperation := opEdit;
    Caption := 'Invoice Editor';
    dtDateOfShoot.Date := DBGrid1.DataSource.DataSet.FieldByName('DATE_OF_SHOOT').AsDateTime;
    dtDueDate.Date := DBGrid1.DataSource.DataSet.FieldByName('INVOICE_DUE_DATE').AsDateTime;
    dtInvoiceDate.Date := DBGrid1.DataSource.DataSet.FieldByName('INVOICE_DATE').AsDateTime;
    dtInvoiceDate.Enabled := False;
    lblInvoiceNo.Caption := DBGrid1.DataSource.DataSet.FieldByName('INVOICE_NO').AsString;
    txtPONo.Text := DBGrid1.DataSource.DataSet.FieldByName('PO_NO').AsString;
    dbcbCompanyName.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('COMPANY_NAME').AsVariant;
    dbcbCompanyName.Enabled := False;
    mmRemark.Lines.Add(DBGrid1.DataSource.DataSet.FieldByName('REMARK').AsString);
    with dbcbProduct.ListSource.DataSet do
    begin
      if not Active then
        Open;
      Last;
      First;
    end;
    dbcbProduct.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('FOR_PRODUCT').AsVariant;
    txtPublishOn.Text := DBGrid1.DataSource.DataSet.FieldByName('PUBLISH_ON').AsString;
    txtPersonInCharge.Text := DBGrid1.DataSource.DataSet.FieldByName('PERSON_IN_CHARGE').AsString;
    txtJobsheetNo.Text := DBGrid1.DataSource.DataSet.FieldByName('JOBSHEET_NO').AsString;
    dbcbPhotographer1.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('PHOTOGRAPHER1_ID').AsInteger;
    dbcbPhotographer2.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('PHOTOGRAPHER2_ID').AsInteger;
    lbTotalAmount.Caption := DBGrid1.DataSource.DataSet.FieldByName('TOTAL_AMOUNT').AsString;
    StatusBar1.Panels[0].Text := 'Created on: ' + DBGrid1.DataSource.DataSet.FieldByName('CREATE_DATE').AsString;
    StatusBar1.Panels[1].Text := 'Updated on: ' + DBGrid1.DataSource.DataSet.FieldByName('UPDATE_DATE').AsString;
    mmFor.Lines.Text := DBGrid1.DataSource.DataSet.FieldByName('FOR').AsString;
    sgDetails.RowCount := DBGrid2.DataSource.DataSet.RecordCount + 1;
    DBGrid2.DataSource.DataSet.First;
    SetLength(DetailsID, DBGrid2.DataSource.DataSet.RecordCount);
    if DBGrid2.DataSource.DataSet.RecordCount > 0 then
      for i := 1 to DBGrid2.DataSource.DataSet.RecordCount do begin
        DetailsID[i - 1] := DBGrid2.DataSource.DataSet.FieldByName('ID').AsInteger;
        sgDetails.Cells[0, i] := IntToStr(i);
        sgDetails.Cells[1, i] := DBGrid2.DataSource.DataSet.FieldByName('DESCRIPTION').AsString;
        sgDetails.Cells[2, i] := DBGrid2.DataSource.DataSet.FieldByName('AMOUNT').AsString;
        sgDetails.Cells[3, i] := DBGrid2.DataSource.DataSet.FieldByName('FOC_DI').AsString;
        DBGrid2.DataSource.DataSet.Next;
      end
    else begin
      sgDetails.RowCount := 2;
      sgDetails.FixedRows := 1;
    end;
    Bookmark := DBGrid1.DataSource.DataSet.GetBookmark;
    Modified := False;
    Show;
  end;
end;

procedure TfmInvoiceList.btnExportPDFClick(Sender: TObject);
begin
  PrintReport(poToPDF);
end;

procedure TfmInvoiceList.chkFilterByYearClick(Sender: TObject);
begin
  DoFilter(True);
end;

procedure TfmInvoiceList.chkFilterByMonthClick(Sender: TObject);
begin
  if not chkFilterByYear.Checked and chkFilterByMonth.Checked then
    chkFilterByYear.Checked := True;
  DoFilter(True);
end;

procedure TfmInvoiceList.DBGrid1CellClick(Column: TColumn);
begin
  StatusBar1.Panels[0].Text := 'Created on: ' + DBGrid1.DataSource.DataSet.FieldByName('CREATE_DATE').AsString;
  StatusBar1.Panels[1].Text := 'Updated on: ' + DBGrid1.DataSource.DataSet.FieldByName('UPDATE_DATE').AsString;
  StatusBar1.Panels[2].Text := 'Created on: ' + DBGrid2.DataSource.DataSet.FieldByName('CREATE_DATE').AsString;
  StatusBar1.Panels[3].Text := 'Updated on: ' + DBGrid2.DataSource.DataSet.FieldByName('UPDATE_DATE').AsString;
end;

procedure TfmInvoiceList.DBGrid1DblClick(Sender: TObject);
begin
  btnEdit2.Click;
end;

procedure TfmInvoiceList.DBGrid1KeyPress(Sender: TObject; var Key: Char);
begin
  case integer(Key) of
    VK_RETURN:
      if DBGrid1.DataSource.DataSet.Bof <> DBGrid1.DataSource.DataSet.Eof then
        btnEdit.Click;
  end;
end;

procedure TfmInvoiceList.DBGrid1TitleClick(Column: TColumn);
var
  ID: integer;
  s: string;
begin
  ID := DBGrid1.DataSource.DataSet.FieldByName('ID').AsInteger;
  with DataModule1.qryInvoiceList do
  begin
    Close;
    s := SQLBeforeClause(SQL.Text, 'ORDER BY') + ' ORDER BY "' + Column.FieldName + '", INVOICE_NO';
    SQL.Clear;
    SQL.Add(s);
    if not Prepared then
      Prepare;
    Open;
    Locate('ID', ID, []);
  end;
//  DBGrid1.DataSource.DataSet.Locate('ID', ID, []);
end;

procedure TfmInvoiceList.DBGrid2CellClick(Column: TColumn);
begin
  StatusBar1.Panels[2].Text := 'Created on: ' + DBGrid2.DataSource.DataSet.FieldByName('CREATE_DATE').AsString;
  StatusBar1.Panels[3].Text := 'Updated on: ' + DBGrid2.DataSource.DataSet.FieldByName('UPDATE_DATE').AsString;
end;

procedure TfmInvoiceList.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FreeAndNil(fmInvoiceList);
end;

procedure TfmInvoiceList.FormCreate(Sender: TObject);
begin
  with DataModule1 do
  begin
    qryInvoiceList.Active := True;
    fbInvoice.Active := True;
    fbInvoiceDetails.Active := True;
    fbCustomers.Active := True;
    fbInvoice.Last;
    fbInvoice.First;
    qryInvoiceList.Last;
    qryInvoiceList.First;
  end;
  txtYear.Enabled := False;
  txtMonth.Enabled := False;
  Updown1.Enabled := False;
  Updown2.Enabled := False;
  frxReport1.AddFunction('function FileExist(s:string): Boolean;');
  frxReport1.AddFunction('function StrReplace(s:string; p1:string; p2:string): string;');
  frxReport1.AddFunction('function ShortMonthNames(i:integer): string;');
end;

procedure TfmInvoiceList.frxReport1GetValue(const VarName: string;
  var Value: Variant);
begin
  if CompareText(VarName, 'CurrencyToWords') = 0 then
    Value := ConvertCurrencyToEnglish(DataModule1.qryPrintInvoice.FieldByName('Total_Amount').AsCurrency);
end;

function TfmInvoiceList.frxReport1UserFunction(const MethodName: string;
  var Params: Variant): Variant;
begin
  if MethodName = 'FILEEXIST' then
    Result := FileExists(Params[0])
  else if MethodName = 'STRREPLACE' then
    Result := StringReplace(Params[0], Params[1], Params[2], [rfReplaceAll])
  else if MethodName = 'SHORTMONTHNAMES' then
    begin
      if Params[0] = varEmpty then
        Result := ''
      else
        Result := FormatSettings.ShortMonthNames[VarToInt(Params[0])];
    end;
end;

procedure TfmInvoiceList.PrintReport(PrintOperation: TPrintOperation);
var
  Path, ReportName: string;
  i: integer;
  InvoiceList: string;
  FieldName: string;
  frMemoView: TfrxMemoView;
begin
  DataModule1.qryPrintInvoice.Active := False;
  if rbInvoice.Checked or rbQuotation.Checked then begin
    with DataModule1.qryPrintInvoice do
    begin
      Params.Clear;
      SQL.Clear;
      InvoiceList := '';
      if DBGrid1.SelectedRows.Count > 0 then
      begin
        for i  := 0 to DBGrid1.SelectedRows.Count - 1 do
        begin
          if InvoiceList <> '' then InvoiceList := InvoiceList + ', ';
          DBGrid1.DataSource.DataSet.GotoBookmark(Pointer(DBGrid1.SelectedRows.Items[i]));
          InvoiceList := InvoiceList + '''' + DBGrid1.DataSource.DataSet.FieldByName('INVOICE_NO').AsString + '''';
        end;
      end else
          InvoiceList := '''' + DBGrid1.DataSource.DataSet.FieldByName('INVOICE_NO').AsString + '''';
      if InvoiceList = '' then begin
         Application.MessageBox('Please select at least one invoice.', 'Warning');

         Exit;
      end;
      SQL.Add('Select INVOICE.INVOICE_NO, ' +
              'INVOICE.PO_NO, ' +
              'INVOICE.INVOICE_DATE, ' +
              'INVOICE.DATE_OF_SHOOT, ' +
              'INVOICE."FOR", ' +
              'INVOICE.FOR_PRODUCT, ' +
              'INVOICE.TOTAL_AMOUNT, ' +
              'INVOICE.PERSON_IN_CHARGE, ' +
              'INVOICE.PUBLISH_ON, ' +
              'INVOICE.JOBSHEET_NO, ' +
              'CUSTOMERS.COMPANY_NAME, ' +
              'CUSTOMERS.ADDRESS, ' +
              'CUSTOMERS.POSTCODE, ' +
              'CUSTOMERS.CITY, ' +
              'CUSTOMERS.STATE, ' +
              'CUSTOMERS.COUNTRY, ' +
              'CUSTOMERS.COMPANY_TEL, ' +
              'CUSTOMERS.COMPANY_FAX, ' +
              'CUSTOMERS.COMPANY_EMAIL, ' +
              'CUSTOMERS.CONTACT_NAME, ' +
              'CUSTOMERS.CONTACT_EMAIL, ' +
              'CUSTOMERS.CONTACT_TEL ' +
              'From ' +
              'INVOICE Inner Join ' +
              'CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ' +
              'Where INVOICE.INVOICE_NO In (' + InvoiceList +')');
//      ParamByName('Invoice_No').Value := DBGrid1.DataSource.DataSet.FieldByName('Invoice_No').AsString;
      if not Prepared then
        Prepare;
      Open;
      if rbInvoice.Checked then
        frxReport1.LoadFromFile('Invoice.fr3', True);
      if rbQuotation.Checked then
        frxReport1.LoadFromFile('Quotation.fr3', True);
    end;
  end else if rbBatchPrint.Checked then begin
    with DataModule1.qryPrintInvoice do
    begin
      Params.Clear;
      SQL.Clear;
      SQL.Add('Select INVOICE.INVOICE_NO, ' +
              'INVOICE.INVOICE_DATE, ' +
              'INVOICE.PO_NO, ' +
              'INVOICE.DATE_OF_SHOOT, ' +
              'INVOICE."FOR", ' +
              'INVOICE.FOR_PRODUCT, ' +
              'INVOICE.TOTAL_AMOUNT, ' +
              'INVOICE.PERSON_IN_CHARGE, ' +
              'INVOICE.PUBLISH_ON, ' +
              'INVOICE.JOBSHEET_NO, ' +
              'CUSTOMERS.COMPANY_NAME, ' +
              'CUSTOMERS.ADDRESS, ' +
              'CUSTOMERS.POSTCODE, ' +
              'CUSTOMERS.CITY, ' +
              'CUSTOMERS.STATE, ' +
              'CUSTOMERS.COUNTRY, ' +
              'CUSTOMERS.COMPANY_TEL, ' +
              'CUSTOMERS.COMPANY_FAX, ' +
              'CUSTOMERS.COMPANY_EMAIL, ' +
              'CUSTOMERS.CONTACT_NAME, ' +
              'CUSTOMERS.CONTACT_EMAIL, ' +
              'CUSTOMERS.CONTACT_TEL ' +
              'FROM ' +
              'INVOICE Inner Join ' +
              'CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ');
      if chkFilterByYear.Checked then begin
        SQL.Add('WHERE Extract(YEAR FROM INVOICE.DATE_OF_SHOOT) = :toYear And ' +
                'Extract(MONTH FROM INVOICE.DATE_OF_SHOOT) = :toMonth');
        SQL.Add('Order By INVOICE.DATE_OF_SHOOT, INVOICE.INVOICE_NO');
        if Params.FindParam('toYear') <> nil then
          ParamByName('toYear').Value := txtYear.Text;
        if Params.FindParam('toMonth') <> nil then
          ParamByName('toMonth').Value := txtMonth.Text;
      end;
      if not Prepared then
        Prepare;
      Open;
      frxReport1.LoadFromFile('Invoice.fr3', True);
    end;
  end else begin
    case RadioGroup1.ItemIndex of
      0: FieldName := 'DATE_OF_SHOOT';
      1: FieldName := 'INVOICE_DATE';
    end;
    with DataModule1.qryPrintInvoice do
    begin
      Params.Clear;
      SQL.Clear;
      SQL.Add('Select INVOICE.INVOICE_NO, ' +
              'INVOICE.PO_NO, ' +
              'INVOICE.INVOICE_DATE, ' +
              'INVOICE.DATE_OF_SHOOT, ' +
              'INVOICE."FOR", ' +
              'INVOICE.FOR_PRODUCT, ' +
              'INVOICE.TOTAL_AMOUNT, ' +
              'INVOICE.AMOUNT_RECEIVED, ' +
              'INVOICE.PERSON_IN_CHARGE, ' +
              'INVOICE.PUBLISH_ON, ' +
              'INVOICE.JOBSHEET_NO, ' +
              'CUSTOMERS.COMPANY_NAME, ' +
              'CUSTOMERS.ADDRESS, ' +
              'CUSTOMERS.POSTCODE, ' +
              'CUSTOMERS.CITY, ' +
              'CUSTOMERS.STATE, ' +
              'CUSTOMERS.COUNTRY, ' +
              'CUSTOMERS.COMPANY_TEL, ' +
              'CUSTOMERS.COMPANY_FAX, ' +
              'CUSTOMERS.COMPANY_EMAIL, ' +
              'CUSTOMERS.CONTACT_NAME, ' +
              'CUSTOMERS.CONTACT_EMAIL, ' +
              'CUSTOMERS.CONTACT_TEL ' +
              'FROM ' +
              'INVOICE Inner Join ' +
              'CUSTOMERS On INVOICE.CUSTOMER_ID = CUSTOMERS.ID ');
      if chkFilterByYear.Checked then
        SQL.Add('Where Extract(YEAR from ' + FieldName+ ') = :toYear ');
      if chkFilterByMonth.Checked then
        SQL.Add(' And Extract(MONTH from ' + FieldName+ ') = :toMonth');
      SQL.Add(' Order By ' + FieldName+ ', INVOICE.INVOICE_NO');
      if Params.FindParam('toYear') <> nil then
        ParamByName('toYear').Value := txtYear.Text;
      if Params.FindParam('toMonth') <> nil then
        ParamByName('toMonth').Value := txtMonth.Text;
      if not Prepared then
        Prepare;
      Open;
      if fmMain.IniConfig.ReadInteger('Config', 'InvoiceListOrientation', 0) = 0 then
        frxReport1.LoadFromFile('InvoiceList.fr3', True)
      else begin
        frxReport1.LoadFromFile('InvoiceList2.fr3', True);
        frMemoView := TfrxMemoView(frxReport1.FindComponent('frxDBDataset1INVOICE_DATE'));
        if frMemoView <> nil then
        begin
          frMemoView.Memo.Clear;
          case RadioGroup1.ItemIndex of
            0: FieldName := 'DATE_OF_SHOOT';
            1: FieldName := 'INVOICE_DATE';
          end;
          frMemoView.Memo.Add('[Uppercase(ShortMonthNames(MonthOf(<frxDBDataset1."' + FieldName+ '">))) + '' '' + IntToStr(YearOf(<frxDBDataset1."' + FieldName+ '">))]');
        end;
      end;
    end;
  end;
  frxReport1.PrepareReport();
  case PrintOperation of
    poPreview: frxReport1.ShowReport;
    poPrint: frxReport1.Print;
    poToPDF:
      begin
        Path := fmMain.IniConfig.ReadString('Config', 'Export Path', '');
        if Path = '' then
          if MessageDlg('Export path is empty, use current path?', mtWarning, mbYesNo, 0) = mrYes then
            Path := ExtractFilePath(Application.ExeName);
        if RightStr(Path, 1) <> '\' then
          Path := Path + '\';
        if rbInvoice.Checked then
          ReportName := DBGrid1.DataSource.DataSet.FieldByName('COMPANY_NAME').AsString + ' - SHOOT ON ' +
                        FormatDateTime('yyyy-mm-dd', DBGrid1.DataSource.DataSet.FieldByName('DATE_OF_SHOOT').AsDateTime) + ' - ' +
                        DBGrid1.DataSource.DataSet.FieldByName('INVOICE_NO').AsString
        else if rbInvoiceList.Checked then
          ReportName := Format('InvoiceList - %d-%d', [UpDown1.Position, UpDown2.Position])
        else if rbBatchPrint.Checked then
          ReportName := Format('InvoiceBatch - %d-%d', [UpDown1.Position, UpDown2.Position]);
        frxPDFExport1.FileName := Path + ReportName;
        frxReport1.Export(frxPDFExport1);
      end;
  end;
end;

procedure TfmInvoiceList.RadioGroup1Click(Sender: TObject);
begin
  DoFilter(Enabled);
end;

procedure TfmInvoiceList.SetFilter(Enabled: Boolean);
begin
  txtYear.Enabled := Enabled;
  txtMonth.Enabled := Enabled;
  UpDown1.Enabled := Enabled;
  UpDown2.Enabled := Enabled;
  UpDown1.Position := YearOf(Date);
  UpDown2.Position := MonthOf(Date);
  chkFilterByYear.Enabled := Enabled;
  chkFilterByMonth.Enabled := Enabled;
  DoFilter(Enabled);
//  if not Enabled then chkFilterByYear.Checked := Enabled;
end;

procedure TfmInvoiceList.DoFilter(Val: Boolean);
var
  ForMonthStr: string;
  FieldName: string;
begin
  case RadioGroup1.ItemIndex of
    0: FieldName := 'DATE_OF_SHOOT';
    1: FieldName := 'INVOICE_DATE';
  end;
  if Active then begin
    with DataModule1.qryInvoiceList do
    begin
      Close;
      SQL.Text := SQLBeforeClause(SQL.Text, 'ORDER BY');
      SQL.Text := SQLBeforeClause(SQL.Text, 'WHERE');
      if Val then
      begin
        if chkFilterByYear.Checked then
          SQL.Add(' WHERE EXTRACT(YEAR FROM ' + FieldName+ ') = ' + txtYear.Text);
        if chkFilterByMonth.Checked then
        begin
          if chkFilterByYear.Checked then
            ForMonthStr := ' AND '
          else
            ForMonthStr := ' WHERE ';
          SQL.Add(ForMonthStr + ' EXTRACT(MONTH FROM ' + FieldName+ ') = ' + txtMonth.Text);
        end;
      end;
      SQL.Add(' ORDER BY ' + FieldName+ ', INVOICE_NO');
      Open;
      Last;
      First;
    end;
{    DBGrid1.DataSource.DataSet.Open;
    DBGrid1.DataSource.DataSet.Last;
    DBGrid1.DataSource.DataSet.First; }
  end;
end;

procedure TfmInvoiceList.txtMonthChange(Sender: TObject);
begin
  DoFilter(True);
end;

procedure TfmInvoiceList.txtYearChange(Sender: TObject);
begin
  DoFilter(True);
end;

procedure TfmInvoiceList.rbBatchPrintClick(Sender: TObject);
begin
  RadioGroup1.Enabled := rbInvoiceList.Checked;
  SetFilter(True);
  chkFilterByYear.Checked := True;
end;

procedure TfmInvoiceList.rbInvoiceClick(Sender: TObject);
begin
  RadioGroup1.Enabled := rbInvoiceList.Checked;
  SetFilter(False);
end;

procedure TfmInvoiceList.rbInvoiceListClick(Sender: TObject);
begin
  RadioGroup1.Enabled := rbInvoiceList.Checked;
  SetFilter(True);
end;

end.
